package com.onemore.task.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.voovan.db.JdbcOperate;
import org.voovan.tools.log.Logger;

import com.alibaba.fastjson.JSONObject;
import com.onemore.task.JdbcGlobal;
import com.onemore.task.OneConfig;

public class ModelOneErp2Wms extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_ERP2WMS");
	}
	static public boolean isNotExist(String tableName)
	{
		return validateTableExist(tableName);
	}
	static private String getFrom()
	{
		String filterDate = "";
		String cangku = "'09001','09002'";
		String ycdfilter = "'1018','2001'";
		String starDate = null;//req.getParameter("starDate");
		String endDate = null;//req.getParameter("endDate");
		Properties properties= OneConfig.getProperties();
		if(properties != null) {
			String str = OneConfig.getProperties().getProperty("cangku");
			if(str != null) {
				cangku = str;
			}

			if(starDate == null) {
				str = OneConfig.getProperties().getProperty("starDate");
				if(str != null) {
					starDate = str;
				}
			}

			str = OneConfig.getProperties().getProperty("ycdfilter");
			if(str != null) {
				ycdfilter = str;
			}
		}
		if(starDate != null && starDate.length() > 0) {
			filterDate = " RQ>='"+starDate+"' AND";
		}else {
			starDate = "2018";
		}
		return "SELECT MAX(wms.BillId) AS BillId, DJBH, CONVERT(VARCHAR(11),MAX(RQ),120) AS CDate,sum(QTYIN) AS QTYIN, SUM (SL) AS SL FROM (select DJBH,RQ,SL from JSEND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from JTSND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from DSEND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from QDTHD where RQ>='"+starDate+"' and YS='1' AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from PSEND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from SDTHD where RQ>='"+starDate+"' and YS='1' AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from FSEND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from FTSND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM2 IN ("+cangku+") UNION ALL select DJBH,RQ,SL from YSEND where RQ>='"+starDate+"' and JZ='1' and ((sp='1' and JS='1') or (sp='0' and JS='0')) AND DM1 IN ("+cangku+") and NOT (dm1 IN ("+ycdfilter+") AND dm2 IN ("+ycdfilter+")) UNION ALL select DJBH,RQ,SL from SPYCD where RQ>='"+starDate+"' and YS='1' AND ISNULL(BYZD3,0)<>'0' AND DM2 IN ("+cangku+") AND YDJH not like 'PF%' AND NOT (dm1 IN ("+ycdfilter+") AND dm2 IN ("+ycdfilter+"))) erp LEFT JOIN ONE_WMS_TZD wms ON erp.DJBH = wms.syncbillid WHERE "+filterDate+" ( wms.BillId IS NULL OR wms.syncbillid IS NULL ) GROUP BY DJBH";
	}
	static public List<Map<String, Object>> getList(String starDate,String endDate) throws SQLException
    {
		String filterDate = "";
		if(starDate != null && starDate.length() > 0) {
			filterDate = " CDate>='"+starDate+"'";
		}else {
			starDate = "2018";
		}
		if(endDate != null && endDate.length() > 0) {
			if(filterDate != null && filterDate.length() > 0) {
				filterDate += " AND";
			}
			filterDate += " CDate<='"+endDate+"'";
		}
		String sql = "SELECT * FROM ONE_ERP2WMS where 1=1 and"+filterDate;
    	List<Map<String, Object>> manyMaps = null;
		manyMaps = getJdbcOperate().queryMapList(sql);
		return manyMaps;
    }
	static public void dropTable() throws SQLException
	{
		String sql = "drop table ONE_ERP2WMS";
    	getJdbcOperate().update(sql);
	}
	static public void importTable() throws SQLException
	{
		dropTable();
		createTable();
	}
    static public void createTable() throws SQLException
    {
    	String sql = "select * into ONE_ERP2WMS from ("+getFrom()+") t";
//		"PRIMARY KEY ([BillId])\r\n" + 
    	getJdbcOperate().update(sql);
    }
}
